import math
import re

import xlrd
from model.UniversityPlanScore import UniversityPlanScore
from model.UniversityScoreRank import UniversityScoreRank
from model.UniversityMajorRank import UniversityMajorRank
from model.UniversityPlanScoreError import UniversityPlanScoreError
from model.University import University
from model.OneScoreRank import OneScoreRank
import sys


# 导入《高考指南》原始数据
def import_score():
    data = xlrd.open_workbook('../static/excel_2023/score_20230611.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    insert_data = list()
    major_type_2 = 0
    universityPlanScore = UniversityPlanScore()
    for i in range(rowNum):
        print(i)
        row = dict()
        if i > 0:
            for j in range(colNum):
                if j == 0:
                    row['year'] = 2022

                if j == 1:
                    if table.cell(i, j).value:
                        row['page'] = int(table.cell(i, j).value)
                    else:
                        print("缺失page: ", i)
                        sys.exit()
                if j == 2:
                    if table.cell(i, j).value:
                        ucode = int(table.cell(i, j).value)
                    else:
                        ucode = 0
                    row['ucode'] = ucode

                if j == 3:
                    if table.cell(i, j).value:
                        uname = table.cell(i, j).value
                    else:
                        uname = ""
                    row['uname'] = uname

                if j == 4:
                    if table.cell(i, j).value:
                        batch = table.cell(i, j).value
                    else:
                        batch = 0
                    row['batch'] = match_code(batch)

                if j == 5:
                    row['major'] = table.cell(i, j).value
                    if row['major'] == '理工类':
                        major_type_2 = 1
                    elif row['major'] == '文史类':
                        major_type_2 = 2
                    else:
                        pass
                    row['major_type_2'] = major_type_2

                if j == 6:
                    if table.cell(i, j).value:
                        row['plan_number'] = int(table.cell(i, j).value)
                    else:
                        row['plan_number'] = 0

                if j == 7:
                    if table.cell(i, j).value:
                        row['submit_number'] = int(table.cell(i, j).value)
                    else:
                        row['submit_number'] = 0

                if j == 8:
                    if table.cell(i, j).value:
                        row['enter_number'] = int(table.cell(i, j).value)
                    else:
                        row['enter_number'] = 0

                if j == 9:
                    row['score'] = table.cell(i, j).value.replace('\n', '')

            print(row)
            # id = universityPlanScore.toInsert(row)
            # print(id)
            # sys.exit()
            insert_data.append(row)
    universityPlanScore.toInsertBatch(insert_data)


def match_code(code):
    if code == '本科第一批':
        ucode = 9
    elif code == '本科第一批预科':
        ucode = 10
    elif code == '本科第二批':
        ucode = 11
    elif code == '精准脱贫专项计划本科批':
        ucode = 12
    elif code == '本科第二批预科A类':
        ucode = 13
    elif code == '本科第二批预科B类':
        ucode = 14
    elif code == '高职高专普通批':
        ucode = 19
    else:
        ucode = 0
    return ucode


def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


""" 
根据《高考指南》原始数据，整理筛选出每个院校（文理科）、每个具体专业的分数。并存入university_score_rank、university_major_rank
"""


def do_score_detail():
    universityPlanScore = UniversityPlanScore()
    universityPlanScoreError = UniversityPlanScoreError()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    data = universityPlanScore.getListBYYearUcode(2021)

    for row in data:
        print(row['id'], row)
        # 去掉空格，将 字母l 替换成 数字1
        score_str = row['score'].replace(" ", "").replace("l", "1")

        # 最后一位是否为符号，不是符号则补全
        if len(score_str) >= 5:
            match_res = re.findall(r"(\d)", score_str[-1])
            if match_res:
                score_str = score_str + '.'

        # 第一位是否为符号，是则去掉
        match_res = re.findall(r"(\d)", score_str[0:1])
        # print(match_res)
        if match_res:
            pass
        else:
            score_str = score_str[1:]
        p_list = []
        s3 = []
        for index, char in enumerate(score_str):
            match_res = re.findall(r"(\d)", char)
            if match_res:
                pass
            else:
                # 第一个字符如果是标点符号，直接跳过
                if index:
                    p_list.append(index)
        # print('score_str', score_str)
        # print('p_list', len(p_list), p_list)
        if p_list:
            start_cut_index = 0
            for i in range(len(p_list)):
                # print(start_cut_index)
                # print(p_list[i])
                s_slice = score_str[start_cut_index:p_list[i]]
                start_cut_index = p_list[i] + 1
                s3.append(s_slice)
        while '' in s3:
            s3.remove('')
        score = s3[::2]
        num = s3[1::2]
        while '' in score:
            score.remove('')
        print('score', score)
        print('num', num)
        university = University()
        university_row = university.getOneByCode(row['ucode'])
        university_score_rank = dict()
        university_score_rank['year'] = row['year']
        university_score_rank['batch'] = row['batch']
        university_score_rank['type'] = row['major_type_2']
        university_score_rank['code'] = row['ucode']
        university_score_rank['name'] = row['uname']
        university_score_rank['score'] = row['score']
        university_score_rank['major'] = row['major']
        if university_row:
            # university_score_rank['type_name'] = university['type_name']
            university_score_rank['f211'] = university_row['f211']
            university_score_rank['f985'] = university_row['f985']
            university_score_rank['dual_class_name'] = university_row['dual_class_name']
            university_score_rank['nature_name'] = university_row['nature_name']
            university_score_rank['province_code'] = university_row['province_code']
            university_score_rank['city_code'] = university_row['city_code']
            university_score_rank['province'] = university_row['province']
            university_score_rank['city'] = university_row['city']
        else:
            # university_score_rank['type_name'] = ''
            university_score_rank['f211'] = 0
            university_score_rank['f985'] = 0
            university_score_rank['dual_class_name'] = 0
            university_score_rank['nature_name'] = 0
            university_score_rank['province_code'] = 0
            university_score_rank['city_code'] = 0
            university_score_rank['province'] = ''
            university_score_rank['city'] = ''
        # 计算平均分、最低分（最后一个算最低分）
        lowest_score = 0
        total_score = 0
        total_person = 0
        error_1 = 0
        if score:
            for k, val in enumerate(score):
                if k <= len(num) - 1 and is_number(val) and is_number(num[k]):
                    score_int = int(val)
                    person_int = int(num[k])
                    if 180 <= score_int < 750 and 0 < person_int < 300:
                        # print(score_int, person_int)
                        if k == len(num) - 1:
                            lowest_score = score_int
                        total_score = total_score + score_int * person_int
                        total_person = total_person + person_int
                    else:
                        if error_1 == 0:
                            print('error_2')
                            # 分数超过3位数或者录取人数超过300
                            insert_error = dict()
                            insert_error['cid'] = row['id']
                            insert_error['year'] = row['year']
                            insert_error['page'] = row['page']
                            insert_error['batch'] = row['batch']
                            insert_error['ucode'] = row['ucode']
                            insert_error['uname'] = row['uname']
                            insert_error['major'] = row['major']
                            insert_error['major_type_2'] = row['major_type_2']
                            insert_error['plan_number'] = row['plan_number']
                            insert_error['submit_number'] = row['submit_number']
                            insert_error['enter_number'] = row['enter_number']
                            insert_error['score'] = row['score']
                            insert_error['remark'] = '分数不合理'
                            universityPlanScoreError.toInsert(insert_error)
                            error_1 = error_1 + 1
                else:
                    if error_1 == 0:
                        print('error_1')
                        # 分数分布分隔后不相等或者分数存在其他字符
                        insert_error = dict()
                        insert_error['cid'] = row['id']
                        insert_error['year'] = row['year']
                        insert_error['page'] = row['page']
                        insert_error['batch'] = row['batch']
                        insert_error['ucode'] = row['ucode']
                        insert_error['uname'] = row['uname']
                        insert_error['major'] = row['major']
                        insert_error['major_type_2'] = row['major_type_2']
                        insert_error['plan_number'] = row['plan_number']
                        insert_error['submit_number'] = row['submit_number']
                        insert_error['enter_number'] = row['enter_number']
                        insert_error['score'] = row['score']
                        insert_error['remark'] = '分数分布分隔后不相等'
                        universityPlanScoreError.toInsert(insert_error)
                        error_1 = error_1 + 1

        # 计算最低分，最低排名，平均分、平均排名
        oneScoreRank = OneScoreRank()
        university_score_rank['lowest_score'] = lowest_score
        lowest_rank = oneScoreRank.getOneByScore(university_score_rank['lowest_score'], row['year'],
                                                 row['major_type_2'])
        if lowest_rank:
            university_score_rank['lowest_rank'] = lowest_rank['total_num']
        else:
            university_score_rank['lowest_rank'] = 0

        # 第一种平均分算法
        if total_score and total_person:
            university_score_rank['mean_score'] = math.ceil(total_score / total_person)
            mean_rank = oneScoreRank.getOneByScore(university_score_rank['mean_score'], row['year'],
                                                   row['major_type_2'])
            if mean_rank:
                university_score_rank['mean_rank'] = mean_rank['total_num']
            else:
                university_score_rank['mean_rank'] = 0
        else:
            university_score_rank['mean_score'] = 0
            university_score_rank['mean_rank'] = 0
        if row['plan_number']:
            university_score_rank['plan_number'] = row['plan_number']
        else:
            university_score_rank['plan_number'] = 0

        if row['submit_number']:
            university_score_rank['submit_number'] = row['submit_number']
        else:
            university_score_rank['submit_number'] = 0

        if row['enter_number']:
            university_score_rank['enter_number'] = row['enter_number']
        else:
            university_score_rank['enter_number'] = 0

        # university_score_rank['is_error'] = error_1
        print('error_1：', error_1, 'insert：', university_score_rank)
        # sys.exit()
        if row['major'] == '理工类' or row['major'] == '文史类':
            # 查询university_score_rank是否有记录，有则修改，无则添加
            count = universityScoreRank.getOneByYearBatch(row['year'], row['batch'], row['major_type_2'], row['ucode'])
            print('院校', count)
            if count:
                # 更新
                print('院校更新', university_score_rank)
                universityScoreRank.toUpdate(university_score_rank, count['id'])
            else:
                # 添加
                score_ins = university_score_rank
                del score_ins['major']
                print(score_ins)
                universityScoreRank.toInsert(score_ins)
        else:
            # 查询university_major_rank是否有记录，有则修改，无则添加
            count = universityMajorRank.getOneByYearBatch(row['year'], row['batch'], row['major_type_2'],
                                                          row['ucode'], row['major'])
            print('专业', count)
            if count:
                # 更新
                universityMajorRank.toUpdate(university_score_rank, count['id'])
                print('专业更新')
            else:
                # 添加
                major_ins = university_score_rank
                major_ins['uname'] = university_score_rank['name']
                major_ins['name'] = university_score_rank['major']
                del major_ins['major']
                print(major_ins)
                universityMajorRank.toInsert(major_ins)


if __name__ == "__main__":
    do_score_detail()
